
package org.crspta.gohomenotes.service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.commons.lang.StringUtils;
import org.crspta.gohomenotes.atozdirectories.AtoZFamilyRecord;
import org.crspta.gohomenotes.atozdirectories.AtoZHouseholdRecord;
import org.crspta.gohomenotes.atozdirectories.AtoZStudentRecord;
import org.crspta.gohomenotes.domain.Address;
import org.crspta.gohomenotes.domain.Family;
import org.crspta.gohomenotes.domain.Person;
import org.crspta.gohomenotes.domain.SchoolClass;
import org.crspta.gohomenotes.domain.SchoolGrade;
import org.crspta.gohomenotes.domain.Student;
import org.crspta.gohomenotes.domain.Teacher;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class AToZDirectoriesImportOperationImpl {
    
    static Logger logger = LoggerFactory.getLogger(AToZDirectoriesImportOperationImpl.class);
    
    private String studentsFilename = "Students.xls";
    
    private String familiesFilename = "Families.xls";
    
    private String householdsFilename = "Households.xls";
    
    private void setStudentsFilename(String studentsFilename) {

        this.studentsFilename = studentsFilename;
    }
    
    private void setFamiliesFilename(String familiesFilename) {

        this.familiesFilename = familiesFilename;
    }
    
    private void setHouseholdsFilename(String householdsFilename) {

        this.householdsFilename = householdsFilename;
    }
    
    /**
     * For initial development. Remove this method when we get it working from a web page.
     */
    public static void main(String[] args) {

        ClassPathXmlApplicationContext appContext = new ClassPathXmlApplicationContext(
        "/META-INF/spring/applicationContext.xml");

        AToZDirectoriesImportOperationImpl importOperation = 
                (AToZDirectoriesImportOperationImpl) appContext.getBean("aToZDirectoriesImportOperation");
        
        importOperation.execute();
    }
    

    @Transactional
    public void execute() {

        List<AtoZHouseholdRecord> householdRecords = this.loadHouseholdRecords();
        
        List<AtoZStudentRecord> studentRecords = this.loadStudentRecords();
        
        for (final AtoZHouseholdRecord householdRecord : householdRecords) {
            logger.debug("Processing Household Record: {}", householdRecord.toString());
     
            if (Family.findFamilysByFamilyId(householdRecord.getLinkFamilyId()).getResultList().size() > 0) {
                logger.error("Family with family ID of {} already exists. The following record will not be processed {}.", 
                        householdRecord.getLinkFamilyId(), householdRecord);
                continue;
            }
            
            Family family = this.assembleFamily(householdRecord);
            family.setFamilyIdType("AtoZDirectory");
            Address address = this.assembleAddress(householdRecord);
            address.persist();
            family.setAddress(address);
            family.persist();
            
            Person parent1 = this.assembleParent1(householdRecord);
            if (parent1 != null) {
                parent1.setFamily(family);
                parent1.persist();
                family.setFather(parent1);
            }
            
            Person parent2 = this.assembleParent2(householdRecord);
            if (parent2 != null) {
                parent2.setFamily(family);
                parent2.persist();
                family.setMother(parent2);
            }
        }
        
        for (final AtoZStudentRecord studentRecord : studentRecords) {
            logger.debug("Processing Student Record: {}", studentRecord.toString());
            String familyId = studentRecord.getLinkFamilyId();
            Family family = Family.findFamilysByFamilyId(familyId).getSingleResult();
            Person child = this.assembleChild(studentRecord);
            child.setFamily(family);
            child.persist();
            if (StringUtils.isNotEmpty(studentRecord.getTeacher())) {
                SchoolClass schoolClass = this.buildSchoolClass(studentRecord);
                Student student = new Student();
                student.setPerson(child);
                student.setSchoolClass(schoolClass);
                student.persist();
            }
        }
    }
    
    private SchoolClass buildSchoolClass(AtoZStudentRecord studentRecord) {

        SchoolClass schoolClass = null;
        Teacher teacher = null;
        List<Teacher> teacherList = Teacher.findTeachersByAbbreviationEquals(
                studentRecord.getTeacher()).getResultList();
        if (teacherList.size() == 0) {
            teacher = new Teacher();
            teacher.setAbbreviation(studentRecord.getTeacher());
            // Create new Person object for the teacher
            Person person = new Person();
            person.setLastName(studentRecord.getTeacher());
            person.setFirstName("Unknown");
            person.persist();
            teacher.setPerson(person);
            // Create new SchoolClass 
            schoolClass = new SchoolClass();
            schoolClass.setTeacher(person);
            
            SchoolGrade schoolGrade = null;
            List<SchoolGrade> schoolGradeList = SchoolGrade.findSchoolGradesByAbbreviationEquals(
                    studentRecord.getGrade()).getResultList();
            if (schoolGradeList.size() == 0) {
                // Create new SchoolGrade
                schoolGrade = new SchoolGrade();
                schoolGrade.setAbbreviation(studentRecord.getGrade());
                schoolGrade.setName(studentRecord.getGrade());
                schoolGrade.persist();
            } else if (schoolGradeList.size() == 1) {
                schoolGrade = schoolGradeList.get(0);
            }
            else {
                throw new IllegalStateException(
                        "More than one SchoolGrade found with abbrevation of "
                                + studentRecord.getGrade());
            }
            schoolClass.setGrade(schoolGrade);
            schoolClass.persist();
            teacher.setSchoolClass(schoolClass);
            teacher.persist();
        } else if (teacherList.size() == 1) {
                teacher = teacherList.get(0);
        }
        else {
            throw new IllegalStateException(
                    "More than one teacher found with abbrevation/last name of "
                            + studentRecord.getTeacher());
        } 
        return schoolClass;
    }

    private Family assembleFamily(AtoZHouseholdRecord householdRecord) {

        Family family = new Family();
        family.setFamilyId(householdRecord.getLinkFamilyId());
        
        return family;
    }
    
    private Person assembleChild(AtoZStudentRecord studentRecord) {

        Person child = new Person();
        child.setFirstName(studentRecord.getFirstName());
        child.setLastName(this.assembleChildLastName(studentRecord));
        child.setNickname(studentRecord.getNickname());
        
        return child;
    }
    
    private String assembleChildLastName(AtoZStudentRecord studentRecord) {
        
        String lastName = StringUtils.substringBefore(studentRecord.getLinkFamilyId(), " (");
        return lastName;
    }
    
    private Person assembleParent1(AtoZHouseholdRecord householdRecord) {

        if (StringUtils.isEmpty(householdRecord.getParent1FirstName())) {
            return null;
        }
        Person parent1 = new Person();
        parent1.setFirstName(householdRecord.getParent1FirstName());
        parent1.setLastName(householdRecord.getParent1LastName());
        parent1.setCellPhone(householdRecord.getParent1MobilePhone());
        parent1.setEmail(householdRecord.getParent1Email());
        parent1.setHomePhone(householdRecord.getHomePhone());
        parent1.setWorkPhone(householdRecord.getParent1WorkPhone());
        return parent1;
    }
    
    private Person assembleParent2(AtoZHouseholdRecord householdRecord) {

        if (StringUtils.isEmpty(householdRecord.getParent2FirstName())) {
            return null;
        }
        Person parent = new Person();
        parent.setFirstName(householdRecord.getParent2FirstName());
        parent.setLastName(householdRecord.getParent2LastName());
        parent.setCellPhone(householdRecord.getParent2MobilePhone());
        parent.setEmail(householdRecord.getParent2Email());
        parent.setHomePhone(householdRecord.getHomePhone());
        parent.setWorkPhone(householdRecord.getParent1WorkPhone());
        
        return parent;
    }
    
    public Address assembleAddress(AtoZHouseholdRecord householdRecord) {

        Address address = new Address();
        address.setAddressLine1(householdRecord.getAddressLine1());
        address.setAddressLine2(householdRecord.getAddressLine2());
        address.setCity(householdRecord.getCity());
        address.setRegionOrState(householdRecord.getState());
        address.setZipCode(householdRecord.getZipCode());
        
        return address;
    }
    
    private List<AtoZStudentRecord> loadStudentRecords() {

        Sheet excelWorksheet = this.loadExcelWorksheet(this.studentsFilename);
        
        List<AtoZStudentRecord> records = new ArrayList<AtoZStudentRecord>();
        int rowCount = excelWorksheet.getRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Cell[] rowData = excelWorksheet.getRow(rowNum);
            int colNum = 0;
            AtoZStudentRecord record = new AtoZStudentRecord();
            record.setStudentId(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setLinkSchoolId(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setLinkFamilyId(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setFirstName(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setMiddleName(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setLastName(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setNickname(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setGrade(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setBirthDate(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setGender(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setEmail(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setPhone(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setTeacher(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCounselor(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setNotes(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField1(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField2(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField3(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField4(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField5(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParentVerification(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setApproveParentAdd(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setAddedByParent(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setTransferStatus(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setLastTransferDate(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setSchoolPriorTransfer(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setGradePriorTransfer(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setStudentCreatedDate(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setStudentModifiedDate(StringUtils.trimToNull(rowData[colNum++].getContents()));
            records.add(record);
        }
        
        logger.info("Number of Student Records: {}", records.size());
                
        return records;
    }
    
    private List<AtoZHouseholdRecord> loadHouseholdRecords() {

        Sheet excelWorksheet = this.loadExcelWorksheet(this.householdsFilename);
        
        List<AtoZHouseholdRecord> records = new ArrayList<AtoZHouseholdRecord>();
        int rowCount = excelWorksheet.getRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Cell[] rowData = excelWorksheet.getRow(rowNum);
            int colNum = 0;
            AtoZHouseholdRecord record = new AtoZHouseholdRecord();
            record.setExcludeFromMsd(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent2FpEmail(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setJoinMailingList(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setPossibleAdvertiser(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent1FpEmail(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setLinkFamilyId(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setLinkSchoolId(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParentId(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setAddressLine1(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setAddressLine2(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCity(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setState(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setZipCode(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setHomePhone(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setSecondPhone(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent1FirstName(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent1LastName(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent1Email(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent1MobilePhone(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent1WorkPhone(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent2FirstName(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent2LastName(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent2Email(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent2MobilePhone(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParent2WorkPhone(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParentCreatedDate(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParentModifiedDate(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setGroupMember(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setDuesCollected(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setPaidForExtraDirectories(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setNumOfExtraDirectories(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setDonationAmount(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setSavingsCard1(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setSavingsCard2(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setSavingsCard3(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setNotes(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField1(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField2(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField3(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField4(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setCustomField5(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setParentVerification(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setPublishOpt(StringUtils.trimToNull(rowData[colNum++].getContents()));
            record.setPublishPreference(StringUtils.trimToNull(rowData[colNum++].getContents()));
            records.add(record);
        }
        
        logger.info("Number of Household Records: {}", records.size());
               
        return records;
    }
    
    private List<AtoZFamilyRecord> loadFamilyRecords() {

        Sheet excelWorksheet = this.loadExcelWorksheet(this.familiesFilename);
        
        List<AtoZFamilyRecord> familyRecords = new ArrayList<AtoZFamilyRecord>();
        int rowCount = excelWorksheet.getRows();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            Cell[] rowData = excelWorksheet.getRow(rowNum);
            int colNum = 0;
            AtoZFamilyRecord atozFamilyRecord = new AtoZFamilyRecord();
            atozFamilyRecord.setFamilyId(StringUtils.trimToNull(rowData[colNum++].getContents()));
            atozFamilyRecord.setStudentLastName(StringUtils.trimToNull(rowData[colNum++].getContents()));
            atozFamilyRecord.setLinkSchoolId(StringUtils.trimToNull(rowData[colNum++].getContents()));
            familyRecords.add(atozFamilyRecord);
        }
        
        logger.info("Number of family: {}", familyRecords.size());
        
        logger.debug("Number of records: {}", familyRecords.get(0).toString());
        
        return familyRecords;
    }
    
    private Sheet loadExcelWorksheet(String excelFilename) {

        ClassPathResource resource = new ClassPathResource(excelFilename);
        InputStream inputStreamReader = null;
        try {
            inputStreamReader = resource.getInputStream();
            inputStreamReader.available();
        }
        catch (IOException ex) {
            logger.error("Could not read excel file: " + resource.getPath(), ex);
        }
        
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(inputStreamReader);
        }
        catch (IOException ex) {
            logger.error("IOException", ex);
        }
        catch (BiffException ex) {
            logger.error("Could not read Excel file: " + resource.getPath(), ex);
        }
        
        logger.debug("Number of Sheets: {}", workbook.getNumberOfSheets());
        
        Sheet excelWorksheet = workbook.getSheet(0);
        
        return excelWorksheet;
    }
}
